﻿using Model;
using ModelDto;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SqlSugar.Extensions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Reflection.Metadata;
using System.Text;
using System.Threading.Tasks;

namespace Common.Complete
{
    public class ExcelHelper : IExcelHelper
    {
        /// <summary>
        /// T的属性必须拥有ExportHelpAttribute，sort是第几列，title是标题
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>

        public MemoryStream ExportXlsx<T>(List<T> data)
        {
            Type type = typeof(T);

            //获取T的拥有ExportHelpAttribute特性的属性
            var properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(t => t.GetCustomAttributes(typeof(ExportHelpAttribute), false).Length > 0).ToList();
            if (!properties.Any())
            {
                throw new Exception("传过来的类没有一个字段有ExportHelp特性");
            }

            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet1");
            IRow headerRow = sheet.CreateRow(0);
            //根据特性的值画excel表头
            foreach (var propertie in properties)
            {
                var propertieAttribute = propertie.GetCustomAttributes<ExportHelpAttribute>().First();
                headerRow.CreateCell(propertieAttribute.Sort).SetCellValue(propertieAttribute.Title);
            }
            //根据List填充Excel数据
            int rowIndex = 1;
            foreach (var item in data)
            {
                //创造列
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (var property in properties)
                {
                    var propertieAttribute = property.GetCustomAttributes<ExportHelpAttribute>().First();
                    Type propertyType = property.PropertyType;
                    switch (propertyType)
                    {
                        case Type DetermineType when DetermineType == typeof(string):
                            string value = property.GetValue(item) == null ? "" : property.GetValue(item).ToString();
                            dataRow.CreateCell(propertieAttribute.Sort).SetCellValue(value);
                            break;
                        case Type DetermineType when DetermineType == typeof(double) || DetermineType == typeof(double?) || DetermineType == typeof(decimal) || DetermineType == typeof(decimal?):
                            if (property.GetValue(item) == null)
                            {
                                dataRow.CreateCell(propertieAttribute.Sort).SetCellValue("");
                            }
                            else
                            {
                                dataRow.CreateCell(propertieAttribute.Sort).SetCellValue(Convert.ToDouble(property.GetValue(item)));
                            }
                            break;
                        case Type DetermineType when DetermineType == typeof(int) || DetermineType == typeof(int?):
                            if (property.GetValue(item) == null)
                            {
                                dataRow.CreateCell(propertieAttribute.Sort).SetCellValue("");
                            }
                            else
                            {
                                dataRow.CreateCell(propertieAttribute.Sort).SetCellValue(Convert.ToInt32(property.GetValue(item)));
                            }

                            break;
                        case Type DetermineType when DetermineType == typeof(DateTime) || DetermineType == typeof(DateTime?):
                            if (property.GetValue(item) == null)
                            {
                                dataRow.CreateCell(propertieAttribute.Sort).SetCellValue("");
                            }
                            else
                            {
                                ICellStyle dateCellStyle = workbook.CreateCellStyle();
                                //设置日期格式
                                short dateFormat = workbook.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss");
                                dateCellStyle.DataFormat = dateFormat;

                                var cell = dataRow.CreateCell(propertieAttribute.Sort);
                                cell.CellStyle = dateCellStyle;
                                cell.SetCellValue(Convert.ToDateTime(property.GetValue(item)));
                            }
                            break;
                        case Type DetermineType when DetermineType == typeof(bool) || DetermineType == typeof(bool?):
                            if (property.GetValue(item) == null)
                            {
                                dataRow.CreateCell(propertieAttribute.Sort).SetCellValue("");
                            }
                            else
                            {
                                bool istrue = Convert.ToBoolean(property.GetValue(item));
                                dataRow.CreateCell(propertieAttribute.Sort).SetCellValue(istrue ? "是" : "否");
                            }
                            break;
                        default:
                            string defaultvalue = property.GetValue(item) == null ? "" : property.GetValue(item).ToString();
                            dataRow.CreateCell(propertieAttribute.Sort).SetCellValue(defaultvalue);
                            break;
                    }
                }
                rowIndex++;
            }
            MemoryStreamHelper ms = new MemoryStreamHelper();
            ms.AllowClose = false;
            workbook.Write(ms);
            workbook.Close();
            ms.Flush();
            ms.Position = 0;
            ms.AllowClose = true;

            return ms;
        }

        public List<T> ImportToList<T>(Stream fileStream)
        {
            IWorkbook workbook = new XSSFWorkbook(fileStream);
            //默认只读第一个sheet，后面需求变动自己拓展
            ISheet sheet = workbook.GetSheetAt(0);

            int rowCount = sheet.LastRowNum;
            List<T> datas = new List<T>();
            Type type = typeof(T);
            var properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(t => t.GetCustomAttributes(typeof(ImportAttribute), false).Length > 0).ToList();
            if (!properties.Any())
            {
                throw new Exception("传过来的类没有一个字段有ImportAttribute特性");
            }
            for (int rowIndex = 0; rowIndex <= rowCount; rowIndex++)
            {
                IRow row = sheet.GetRow(rowIndex);
                if (row == null) continue; // 跳过空行
                T t = Activator.CreateInstance<T>(); //构造新实例
                //给实例属性赋值
                foreach (var propertie in properties)
                {
                    var propertieAttribute = propertie.GetCustomAttributes<ExportHelpAttribute>().First();             
                    //以此类推，看属性的类型转换成你需要的类型，在CellValue方法找对应的转换方法
                    switch (propertie.PropertyType)
                    {
                        case Type DetermineType when DetermineType == typeof(string):
                            propertie.SetValue(t, row.GetCell(propertieAttribute.Sort).StringCellValue);
                            break;
                        default:
                            break;

                    }
                }
                datas.Add(t);
            }

            return datas;
        }
    }
}
